﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class GuiaFaturamentoRepository : IRepository<GuiaFaturamento>
    {
        private IDbCommand Command { get; set; }

        public GuiaFaturamentoRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public GuiaFaturamento GetById(string numeroGuia, int loteGuia)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, [local], idLoteGuia,dataEmissao,glosa,reapresentada FROM tbGuiaFaturamento WHERE numeroGuia='" + 
                    numeroGuia + "' and idLoteGuia=" + loteGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    GuiaFaturamento guia = new GuiaFaturamento();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.IDLoteGuia = int.Parse(reader["idLoteGuia"].ToString());
                    guia.DataEmissao = DateTime.Parse(reader["dataEmissao"].ToString());
                    guia.Glosa = Convert.ToBoolean(reader["glosa"].ToString());
                    guia.Reapresentada = Convert.ToBoolean(reader["reapresentada"].ToString());

                    return guia;
                }
                else
                {
                    throw new Exception("Guia não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<GuiaFaturamento> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, [local],idLoteGuia,dataEmissao,glosa,reapresentada FROM tbGuiaFaturamento";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<GuiaFaturamento> list = new List<GuiaFaturamento>();
                while (reader.Read())
                {
                    GuiaFaturamento guia = new GuiaFaturamento();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.IDLoteGuia = int.Parse(reader["idLoteGuia"].ToString());
                    guia.DataEmissao = DateTime.Parse(reader["dataEmissao"].ToString());
                    guia.Glosa = Convert.ToBoolean(reader["glosa"].ToString());
                    guia.Reapresentada = Convert.ToBoolean(reader["reapresentada"].ToString());

                    list.Add(guia);
                }
                return list.AsQueryable<GuiaFaturamento>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(GuiaFaturamento entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Empty;
                Command.CommandText = "INSERT INTO tbGuiaFaturamento (numeroGuia, [local], idLoteGuia) VALUES ('" + entity.NumeroGuia + "','" + entity.Local +
                    "'," + (long)entity.IDLoteGuia + ")";
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();

                return 0;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(GuiaFaturamento entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbGuiaFaturamento WHERE numeroGuia='" + entity.NumeroGuia + "' and idLoteGuia=" + entity.IDLoteGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(GuiaFaturamento entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("UPDATE tbGuiaFaturamento SET [local]='{1}',glosa={3},reapresentada={4} WHERE numeroGuia='{0}' and idLoteGuia={2}", 
                    entity.NumeroGuia, entity.Local, entity.IDLoteGuia, entity.Glosa, entity.Reapresentada);
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
